This is an interactive Jupyter notebook that uses API socket to extract latest & historic lending series from Bank of England's Interactive Database (IADB). Holdings of Mortgage securities and Lending data are aggregated across all UK Resident Banks (Monetary Financial Institutions);
Notebook covers:
All values are displayed in £ millions
Section 3 of notebook plots mortgage series scatter plot and distribution curve from all historic data points in a selected target range of BOE Bank Rate.
Developed by Victor Zommers | Check out other dashboards
%%HTML
<script src="require.js"></script>
#!pip install requests #and other packages needed
import requests
import io
import scipy
import datetime
from datetime import timedelta
import urllib3
import warnings
warnings.filterwarnings('ignore')
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
import numpy as np
import pandas as pd
import plotly.graph_objs as go
from plotly.subplots import make_subplots
from scipy.stats import gaussian_kde
from ipywidgets import interact, FloatRangeSlider, Dropdown
We will also create 2 custom series:
boe_series = {'Datefrom':'01/Apr/1993','SeriesCodes':'IUQABEDR,LPQB3VB,LPQVTVQ,LPMB7GT,RPQTFHD,LPMB8GO','CSVF':'TN','UsingCodes':'Y','VPD':'N','VFD':'N'}
url2 = 'http://www.bankofengland.co.uk/boeapps/iadb/fromshowcolumns.asp?csv.x=yes'
headers = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) ' 'AppleWebKit/537.36 (KHTML, like Gecko) ' 'Chrome/54.0.2840.90 ' 'Safari/537.36'}
response = requests.get(url2, params=boe_series, headers=headers,verify = False)
df2 = pd.read_csv(io.BytesIO(response.content))
df2['DATE'] = pd.to_datetime(df2['DATE'],format='%d %b %Y')
def format_quarter_year(date):
return f"{date.year} Q{date.quarter}"
df2['Quarter'] = df2['DATE'].apply(format_quarter_year)
sums = df2.groupby('Quarter')['LPMB7GT'].transform('sum')
df2['LPQB7GT'] = np.where(sums == 0, np.nan, sums)
sums = df2.groupby('Quarter')['LPMB8GO'].transform('sum')
df2['LPQB8GO'] = np.where(sums == 0, np.nan, sums)
df2 = df2.drop_duplicates(subset='Quarter', keep='last')
df2['Sec_Pct'] = df2['LPQB3VB']/df2['LPQB7GT']*100
df2['Write_Pct'] = df2['RPQTFHD']/df2['LPQB7GT']*100
df2.tail()
| DATE | IUQABEDR | LPQB3VB | LPQVTVQ | LPMB7GT | RPQTFHD | LPMB8GO | Quarter | LPQB7GT | LPQB8GO | Sec_Pct | Write_Pct | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 219 | 2022-09-30 | 1.6172 | 76942.0 | 82420.0 | 42432.0 | 16.0 | -940.0 | 2022 Q3 | 128892.0 | -1727.0 | 59.694938 | 0.012413 |
| 222 | 2022-12-31 | 2.8056 | 74230.0 | 56510.0 | 40698.0 | -5.0 | -616.0 | 2022 Q4 | 123453.0 | -1722.0 | 60.128146 | -0.004050 |
| 225 | 2023-03-31 | 3.8555 | 54359.0 | 47424.0 | 39859.0 | 13.0 | -573.0 | 2023 Q1 | 120687.0 | -838.0 | 45.041305 | 0.010772 |
| 228 | 2023-06-30 | 4.4583 | 47843.0 | 57158.0 | 42836.0 | 13.0 | -141.0 | 2023 Q2 | 126092.0 | 2977.0 | 37.942931 | 0.010310 |
| 231 | 2023-09-30 | 5.1602 | 57146.0 | 49134.0 | 41143.0 | NaN | 930.0 | 2023 Q3 | 124338.0 | -1693.0 | 45.960205 | NaN |
Execute the cell below to plot all residential mortgage series side-by-side. Time range can be controlled by a range slider at the bottom of the plot. Use home button in top-right corner to reset the view.
Note the persistent trend of decrease of securitised mortgages held by aggregate banking sector post-GFC. This was largely replaced by holdings of financial derivatives across the balance sheets (derivatives are reported as net liability, therefore negative value of net liabilities = increase in assets).
Meanwhile, new gross lending peaked at ~60% of all securitised mortgages held in Dec 2022. This indicates cleansing of balance sheet and overall displacement of old legacy debt with new originations in the total stock of securitised mortgages.
fig = make_subplots(rows=7, cols=1, shared_xaxes=True)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['IUQABEDR'], mode='lines', name='Average BOE rate', fill='tozeroy'), row=1, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['LPQB3VB'], mode='lines', name='Gross mortgage lending by Banks', fill='tozeroy'), row=2, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['LPQVTVQ'], mode='lines', name='Value of new mortgage approvals (lead)', fill='tozeroy'), row=3, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['LPMB7GT'], mode='lines', name='Securitised mortgages held by Banks',fill='tozeroy'), row=4, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['Sec_Pct'], mode='lines', name='Gross lending as % of securitised mortgages held', fill='tozeroy'), row=5, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['RPQTFHD'], mode='lines', name='Write-offs of Mortgages', fill='tozeroy'), row=6, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=[0 for _ in df2['DATE']], mode='lines', showlegend=False), row=7, col=1)
fig.update_yaxes(visible=False, showticklabels=False,showgrid=False, zeroline=False, row=7)
fig.update_xaxes(showgrid=False, zeroline=False,rangeslider_visible=True, row=7, col=1)
fig.update_layout(height=750, width=1000, template="simple_white") # Adjust height and width as needed
fig.show() # render in google collab, use just fig.show() if local machine
Explore how historic density distribution of gross lending and securitisation activity changes across different monetary policy regimes.
default_column = 'LPQB3VB'
data = df2[default_column]
# Handle NaNs for Histogram and KDE
clean_data = data.replace([np.inf, -np.inf], np.nan).dropna()
kde = gaussian_kde(clean_data)
y_kde = np.linspace(clean_data.min(), clean_data.max(), 50)
x_kde = kde(y_kde)
# Create plots
scatter = go.Scatter(x=df2['DATE'], y=data, mode='markers', name='Scatter')
histogram = go.Histogram(y=clean_data, orientation='h', nbinsy=10, name='Histogram', histnorm='probability density')
kde_trace = go.Scatter(y=y_kde, x=x_kde, mode='lines', name='KDE',line=dict(color='black'))
# Combine into a single interactive figure
fig_combined = go.FigureWidget(make_subplots(rows=1, cols=2, subplot_titles=('Scatter', 'Histogram & KDE')))
fig_combined.update_layout(height=500, width=1000,template="simple_white")
fig_combined.add_trace(scatter, row=1, col=1)
fig_combined.add_trace(histogram, row=1, col=2)
fig_combined.add_trace(kde_trace, row=1, col=2)
# Update scatter plot and histogram in fig_combined based on slider and dropdown values
def update_plot(column, range_vals):
mask = (df2['IUQABEDR'] >= range_vals[0]) & (df2['IUQABEDR'] <= range_vals[1])
filtered_data = df2[mask]
selected_data = filtered_data[column]
clean_selected_data = selected_data.replace([np.inf, -np.inf], np.nan).dropna()
# Update plots
fig_combined.data[0].x = filtered_data['DATE'].tolist()
fig_combined.data[0].y = selected_data.tolist()
fig_combined.data[1].y = clean_selected_data.tolist()
# Update KDE
kde = gaussian_kde(clean_selected_data)
y_kde = np.linspace(clean_selected_data.min(), clean_selected_data.max(), 50)
x_kde = kde(y_kde)
fig_combined.data[2].y = y_kde
fig_combined.data[2].x = x_kde
# Update plot render in google collab \\ remove completely if running on local machine
# Dropdown for column selection
dropdown = Dropdown(
options=df2.columns[(df2.dtypes != 'datetime64[ns]') & (df2.dtypes != 'object')].tolist(),
value=default_column,
description='Series:')
# Float range slider for bank rate
min_val2, max_val2 = df2['IUQABEDR'].min(), df2['IUQABEDR'].max()
slider = FloatRangeSlider(
value=[min_val2, max_val2],
min=min_val2,
max=max_val2,
step=0.01,
description='BOE Rate:',
continuous_update=True)
interact(update_plot, column=dropdown, range_vals=slider)
fig_combined